import pymysql


class DBTools(object):
    # 建立连接
    @staticmethod
    def create_conn():
        # 指定 ip、port、用户名、密码、数据库、返回连接
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2022", database="ihrm", charset="utf8")
        return conn

    # 查询方法
    @staticmethod
    def query_one(sql):
        # 获取连接  创建游标  执行SQL  提取结果  返回一条结果
        cursor=None
        conn=None
        res=None
        try:
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchone()
        except Exception as err:
            print("查询SQL失败",err)
        finally:
            cursor.close()
            conn.close()
            return res
    @staticmethod
    def query_many(sql,n):
        cursor=None
        conn=None
        res=None
        # 获取连接  创建游标  执行SQL  提取结果  返回多条结果
        try:
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            cursor.execute(sql)
            res = cursor.fetchmany(n)
        except Exception as err:
            print("查多条失败",err)
        finally:
            cursor.close()
            conn.close()
            return res





    def query_all(self,sql):
        # 获取连接  创建游标  执行SQL  提取结果  返回全部结果
        pass
    @staticmethod
    def db_idu(sql):
        # 增删改
        conn = None
        cursor = None
        try:
            # 3. 创建游标
            conn = DBTools.create_conn()
            cursor = conn.cursor()
            res = cursor.execute(sql )
            conn.commit()
            print("增删改成功", res)

        except Exception as err:
            conn.rollback()
            print("增删改失败", err)
        finally:
            # 6. 关闭游标
            cursor.close()
            # 7. 关闭连接
            conn.close()


if __name__ == '__main__':
    res=DBTools.query_one('select * from t_book;')
    print("查一条",res )
    res1=DBTools.query_many('select * from t_book;',3)
    print("查多条",res1)
    DBTools.db_idu("insert into t_book (id,title,pub_date) values (2026,'新年快乐','1986-01-01');")
    DBTools.db_idu("UPDATE t_book set `read` = `read`+1 where title = '飞起';")
    DBTools.db_idu("DELETE from t_book where id = 2025")